#-*- coding:utf-8 -*-
'''
author = Alvin Gan
'''

import datetime
import os
import win32com.client as win32
import time
import itchat
from PIL import ImageGrab
import shutil
import logging



# 判断是否存在指定的文件夹，如果不存在则新建
def mkdir(path):
    folder = os.path.exists(path)
    if not folder:
        os.makedirs(path)
    else:
        pass

# 获取前几日的日期列表
def get_date_list(n):
    now_date = datetime.datetime.now()
    date_list = []
    for i in range(0, n):
        new_date = now_date - datetime.timedelta(i)
        date_list.append(new_date.strftime('%m/%d/%Y'))
    return date_list

# 获取前n日的日期
def get_date(n):
    now_date = datetime.datetime.now()
    new_date = now_date - datetime.timedelta(n)
    return new_date.strftime('%#m/%#d/%Y')      # 井号可以去掉单数日期和月份前面的数字0，比如 05月 会返回 5月


send_date = datetime.datetime.now().strftime('%Y%m%d')
before_date = (datetime.datetime.now()- datetime.timedelta(1)).strftime('%Y%m%d')
yesterday = get_date(1)   # 这个日期一会儿需要改一下啊 别忘了
hide_date = get_date(5)
path = 'D:/ribao/file/' + before_date + '.xlsx'    # 要操作的Excel路径
mkdir('D:/ribao/pic/' + send_date)     # 创建一下存放图片的路径
path2 = 'D:/ribao/pic/' + send_date    # 要存放图片的路径

logging.basicConfig(level=logging.DEBUG,
                    format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S',
                    filename='D:/ribao/rizhi/'+ send_date + '.log',
                    filemode='w')

logging.info('send_date:' + send_date)
logging.info('before_date:' + before_date)
logging.info('yesterday:' + yesterday)


logging.info('设定为Excel文件,并打开%s' % path)
excel = win32.Dispatch('Excel.Application')
wb = excel.Workbooks.open(path)   # 打开excel文件
# sheet_lists = ['安装花费汇总','日活留存汇总','安卓汇总','安卓分渠道','IOS汇总','IOS分渠道','ROI7','中东分层分国家','收入透视','花费透视']
logging.info('执行全部刷新操作...')
wb.RefreshAll()
logging.info('等待60s...')
time.sleep(60)

try:
    logging.info('收入透视和花费透视刷新')
    # 收入透视和花费透视刷新
    sheet1 = wb.Worksheets('收入透视')
    sheet1.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet2 = wb.Worksheets('花费透视')
    sheet2.PivotTables('数据透视表1').PivotCache().Refresh()
    #
    #
    logging.info('安装花费汇总')
    # # 安装花费汇总
    sheet3 = wb.Worksheets('安装花费汇总')
    sheet3.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet3.PivotTables('数据透视表1').PivotFields('日期').PivotItems(yesterday).Visible = True
    sheet3.Range('A1:J33').CopyPicture()  # 将选定区域复制为图片
    sheet3.Paste(sheet3.Range('BT1'))  # 将图片粘贴到指定区域
    sheet3.Shapes('Picture 1').copy()           # 复制图片
    img3 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img3.save(path2 + '/huafei.png')                  # 保存图片
    sheet3.Shapes('Picture 1').delete()        # 从excel中删除原图
    #
    #
    #
    #
    logging.info('日活留存汇总')
    # # 日活留存汇总
    sheet4 = wb.Worksheets('日活留存汇总')
    sheet4.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet4.PivotTables('数据透视表1').PivotFields(' 日期').PivotItems(yesterday).Visible = True     # 这个日期这儿有空格
    sheet4.PivotTables('数据透视表1').PivotFields(' 日期').PivotItems(hide_date).Visible = False  # 这个日期这儿有空格
    sheet4.Range('C1:K39').CopyPicture()  # 将选定区域复制为图片
    sheet4.Paste(sheet4.Range('BT1'))  # 将图片粘贴到指定区域
    sheet4.Shapes('Picture 1').copy()           # 复制图片
    img = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img.save(path2 + '/dau.png')                  # 保存图片
    sheet4.Shapes('Picture 1').delete()        # 从excel中删除原图

    logging.info('安卓汇总')
    # # 安卓汇总
    sheet5 = wb.Worksheets('安卓汇总')
    sheet5.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet5.PivotTables('数据透视表1').PivotFields('日期').PivotItems(yesterday).Visible = True
    sheet5.PivotTables('数据透视表1').PivotFields('日期').PivotItems(hide_date).Visible = False
    sheet5.Range('A1:I29').CopyPicture()  # 将选定区域复制为图片
    sheet5.Paste(sheet5.Range('BT1'))  # 将图片粘贴到指定区域
    sheet5.Shapes('Picture 1').copy()           # 复制图片
    img5 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img5.save(path2 + '/android.png')                  # 保存图片
    sheet5.Shapes('Picture 1').delete()        # 从excel中删除原图
    #
    #
    # # 安卓分渠道
    logging.info('安卓分渠道')
    sheet6 = wb.Worksheets('安卓分渠道')
    sheet6.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet6.PivotTables('数据透视表1').PivotFields('日期').PivotItems(yesterday).Visible = True
    sheet6.PivotTables('数据透视表1').PivotFields('日期').PivotItems(hide_date).Visible = False
    sheet6.Range('A1:T30').CopyPicture()  # 将选定区域复制为图片
    sheet6.Paste(sheet6.Range('BT1'))  # 将图片粘贴到指定区域
    sheet6.Shapes('Picture 1').copy()           # 复制图片
    img6 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img6.save(path2 + '/android_channel.png')                  # 保存图片
    sheet6.Shapes('Picture 1').delete()        # 从excel中删除原图
    #
    #
    # # IOS汇总
    logging.info('IOS汇总')
    sheet7 = wb.Worksheets('IOS汇总')
    sheet7.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet7.PivotTables('数据透视表1').PivotFields('日期').PivotItems(yesterday).Visible = True
    sheet7.PivotTables('数据透视表1').PivotFields('日期').PivotItems(hide_date).Visible = False
    sheet7.Range('A1:J29').CopyPicture()  # 将选定区域复制为图片
    sheet7.Paste(sheet7.Range('BT1'))  # 将图片粘贴到指定区域
    sheet7.Shapes('Picture 1').copy()           # 复制图片
    img7 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img7.save(path2 + '/ios.png')                  # 保存图片
    sheet7.Shapes('Picture 1').delete()        # 从excel中删除原图
    #
    #
    # # IOS分渠道
    logging.info('IOS分渠道')
    sheet8 = wb.Worksheets('IOS分渠道')
    sheet8.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet8.PivotTables('数据透视表1').PivotFields('日期').PivotItems(yesterday).Visible = True
    sheet8.PivotTables('数据透视表1').PivotFields('日期').PivotItems(hide_date).Visible = False
    sheet8.Range('A1:Z30').CopyPicture()  # 将选定区域复制为图片
    sheet8.Paste(sheet8.Range('BT1'))  # 将图片粘贴到指定区域
    sheet8.Shapes('Picture 1').copy()           # 复制图片
    img8 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img8.save(path2 + '/ios_channel.png')                  # 保存图片
    sheet8.Shapes('Picture 1').delete()        # 从excel中删除原图


    # ROI7

    logging.info('ROI7')
    sheet9 = wb.Worksheets('ROI7')
    roi_visible_date = get_date(6)
    roi_unvisible_date = get_date(30)
    sheet9.PivotTables('数据透视表1').PivotCache().Refresh()
    sheet9.PivotTables('数据透视表1').PivotFields('日期').PivotItems(roi_visible_date).Visible = True
    sheet9.PivotTables('数据透视表1').PivotFields('日期').PivotItems(roi_unvisible_date).Visible = False
    sheet9.Shapes('图表 1').copy()           # 复制图片
    img9 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img9.save(path2 + '/roi.png')                  # 保存图片



    # 中东分层分国家

    logging.info('中东分层分国家')
    pivot_lists = ["数据透视表1","数据透视表2","数据透视表3","数据透视表4"]
    sheet10 = wb.Worksheets('中东分层分国家')
    ar_hide_date = get_date(11)
    for pivot_list in pivot_lists:
        sheet10.PivotTables(pivot_list).PivotCache().Refresh()
        sheet10.PivotTables(pivot_list).PivotFields("日期").PivotItems(yesterday).Visible = True
        sheet10.PivotTables(pivot_list).PivotFields("日期").PivotItems(ar_hide_date).Visible = False
    # 中东level1
    logging.info('中东level1')
    sheet10.Range('A1:AO16').CopyPicture()  # 将选定区域复制为图片
    sheet10.Paste(sheet10.Range('BT1'))  # 将图片粘贴到指定区域
    sheet10.Shapes('Picture 1').copy()           # 复制图片
    img10 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img10.save(path2 + '/level1.png')                  # 保存图片
    sheet10.Shapes('Picture 1').delete()        # 从excel中删除原图

    # 中东level2
    logging.info('中东level2')
    sheet10.Range('A21:AY36').CopyPicture()  # 将选定区域复制为图片
    sheet10.Paste(sheet10.Range('BT2'))  # 将图片粘贴到指定区域
    sheet10.Shapes('Picture 2').copy()           # 复制图片
    img10 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img10.save(path2 + '/leve2.png')                  # 保存图片
    sheet10.Shapes('Picture 2').delete()        # 从excel中删除原图

    # 中东level3
    logging.info('中东level3')
    sheet10.Range('A41:K56').CopyPicture()  # 将选定区域复制为图片
    sheet10.Paste(sheet10.Range('BT3'))  # 将图片粘贴到指定区域
    sheet10.Shapes('Picture 3').copy()           # 复制图片
    img10 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img10.save(path2 + '/leve3.png')                  # 保存图片
    sheet10.Shapes('Picture 3').delete()        # 从excel中删除原图

    # 中东 总
    logging.info('中东 总')
    sheet10.Range('A66:AE78').CopyPicture()  # 将选定区域复制为图片
    sheet10.Paste(sheet10.Range('BT4'))  # 将图片粘贴到指定区域
    sheet10.Shapes('Picture 4').copy()           # 复制图片
    img10 = ImageGrab.grabclipboard()     # 将复制的图片抓取到剪切板
    img10.save(path2 + '/zhongdong.png')                  # 保存图片
    sheet10.Shapes('Picture 4').delete()        # 从excel中删除原图


    # 设置要发送的文本
    logging.info('设置要发送的文本')
    sheet11 = wb.Worksheets('文案')
    msg1 = sheet11.Range('F3').value
    msg2 = sheet11.Range('F5').value
    msg3 = sheet11.Range('F16').value
    msg4 = sheet11.Range('F26').value
    msg5 = sheet11.Range('F27').value
    msg_text = msg1 + '\n' + msg2 +'\n' + msg3 +'\n' + msg4 + '\n' + msg5     #日报文本
    logging.info(msg_text)
except Exception as e:
    logging.info('程序中断，错误代码:%s' % str(e))
finally:
    logging.info('设置要发送的文本')
    sheet11 = wb.Worksheets('文案')
    msg1 = sheet11.Range('F3').value
    msg2 = sheet11.Range('F4').value
    msg3 = sheet11.Range('F5').value
    msg4 = sheet11.Range('F6').value
    msg5 = sheet11.Range('F7').value
    msg_text = msg1 + '\n' + msg2 + '\n' + msg3 + '\n' + msg4 + '\n' + msg5  # 日报文本
    logging.info(msg_text)

logging.info('关闭Excel程序')
wb.Close(SaveChanges = True)
excel.Quit()

logging.info('复制为新文件')
new_name ='D:/ribao/file/' + send_date + '.xlsx'    # 将旧文件存为新文件
shutil.copyfile(path,new_name)     # 复制已经更新的excel文件，保存为今日新文件







# 下面是登录微信，发送图片和文本
# logging.info('登录微信')
itchat.auto_login(hotReload= True)   # 登录微信网页版，热登录，希望不要让我重新登录

# 获取目标人的username
def get_username(nickname):
    try:
        username = itchat.search_friends(nickname)[0]['UserName']
        return username
    except Exception as e:
        print(e)
        pass

# 发送图片
def send_image(path, username):
    try:
        result = itchat.send_image(path, username)
        if list(result.values())[0]['ErrMsg'] == '请求成功':
            logging.info('发送成功')
        else:
            logging.info('发送失败')
    except:
        print('fail')


# 获取指定路径下的图片文件的绝对路径
def get_file_path(path):
    file_path = []
    lis_file = os.listdir(path)
    for file in lis_file:
        if os.path.splitext(file)[1] == '.png':
            file_path.append(os.path.join(path, file))
    return file_path




try:
    logging.info('获取图片文件列表')
    file_path = get_file_path(path2)
    nickname = '阿甘'
    user_name = get_username(nickname)
    for file in file_path:
        send_image(file, user_name)
        time.sleep(1)
    itchat.send(msg_text, user_name)
    itchat.send_file('D:/ribao/rizhi/' + send_date + '.log', user_name)
except Exception as e:
    logging.info('发送文件时出错，错误代码 %s' % str(e))


print("程序结束")







